package com.seed.commons.dbutil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DB implements Serializable {

	private static final long serialVersionUID = 1L;

	//private Log log = LogFactory.getLog(getClass());

	/** 数据源 */
	private static ComboPooledDataSource ds;

	private static Connection connect;

	public DB(ComboPooledDataSource c3p0ds) {
		ds = c3p0ds;
	}

	public static Connection getConnect() throws SQLException {
		if (null == connect || connect.isClosed()) {
			connect = ds.getConnection();
		}
		return connect;
	}

	/**
	 * 修改数据
	 * 
	 * @param sql
	 * @param val
	 * @return 修改记录数
	 * @throws Exception
	 */
	public static boolean execute(String sql, Object[] val) throws Exception {
		PreparedStatement pstmt = getConnect().prepareStatement(sql);
		setParams(pstmt, val);
		return pstmt.execute() ;
	}

	/**
	 * 插入数据
	 * 
	 * @param sql
	 * @param val
	 * @return true/false
	 * @throws Exception
	 */
	public static boolean insert(String sql, Object[] val) throws Exception {
		PreparedStatement pstmt = null;
		
		pstmt = getConnect().prepareStatement(sql);

		setParams(pstmt, val);
		return pstmt.execute();
	}
	
	/**
	 * 查询多条记录
	 * 
	 * @param sql
	 *            查询语句
	 * @param queryParams
	 *            查询条件参数
	 * @return 返回List集合
	 * @throws Exception
	 */
	public static List<DBBean> query(String sql, Object[] queryParams)
			throws Exception {
		ArrayList<DBBean> ls = new ArrayList<DBBean>();

		PreparedStatement pstmt = getConnect().prepareStatement(sql);
		setParams(pstmt, queryParams);
		
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			DBBean bean = new DBBean();

			for (int i = 1; i <= pstmt.getMetaData().getColumnCount(); i++) {
				String field = pstmt.getMetaData().getColumnName(i);
				Serializable seriVal = (Serializable) rs.getObject(i);
				bean.setbean(field, seriVal);
			}
			ls.add(bean);
		}
		return ls;
	}

	/**
	 * 查询一条记录
	 * 
	 * @param sql
	 * @param queryParams
	 * @return
	 * @throws Exception
	 */
	public static DBBean getBeanFirst(String sql, Object[] queryParams)
			throws Exception {
		List<DBBean> list = query(sql, queryParams);

		if (list.isEmpty())
			return null;

		return list.get(0);
	}

	@SuppressWarnings("unchecked")
	protected static void setParams(PreparedStatement pstmt,
			Object[] queryParams) throws SQLException {
		if (null != queryParams && queryParams.length > 0) {
			for (int i = 0; i < queryParams.length; i++) {
				Object qp = queryParams[i];
				if (qp != null && qp instanceof List) {
					List<Serializable> list = (List<Serializable>) qp;
					for (Serializable seri : list) {
						pstmt.setObject(i + 1, seri);
					}
				} else {
					pstmt.setObject(i + 1, queryParams[i]);
				}
			}
		}
	}
	
	
}
